要給SQL SERVER存儲過程傳遞數組參數，應怎麼辦？
 

--------------------------------------------------------------------------------
 
  發表時間：2003-5-19  
 
 
吉祥365

Q. How can I pass an array of values to a SQL Server stored-procedure.
(v1.1 1999.05.14)

A. Basically you can't - SQL Server has no array type - ANSI SQL 92 does not specify array support. But there are various ways around it.

確切的說不行-SQL SERVER沒有數組類型，ANSI SQL 92標準也不支持數組。但可用其它的方法來實現。

1. You could simulate an array by passing one or more varchar(255) fields with comma-separated values and then use a WHILE loop with PATINDEX and SUBSTR to extract the values.

1、你可以使用幾個VARCHAR（255）字段來模擬數組，字段中用逗號分開各個數據，然後使用循環和PATINDEX和SUBSTR分開這些數據。

2. The more usual way to do this would be to populate a temporary table with the values you need and then use the contents of that table from within the stored-procedure. Example of this below

2、通常這種方法需要為這些數據創建一個臨時表，然後在存儲過程使用表中的內容。如下例

create procedure mytest @MyParmTempTable varchar(30)
as
begin
-- @MyParmTempTable contains my parameter list...   這個變量是包含參數的表名

-- For simplicity use dynamic sql to copy into a normal temp table...

create table #MyInternalList (
list_item varchar( 2 ) not null
)

set nocount on

insert #MyInternalList
exec ( "select * from " + @MyParmTempTable )

set nocount off

-- It is now easier to join..
select *
from sysobjects
where type in ( select list_item from #MyInternalList )

end
go

To call..

create table #MyList (
list_item varchar( 2 ) not null
)
insert #MyList values ( 'S' )
insert #MyList values ( 'U' )
insert #MyList values ( 'P' )

exec mytest "#MyList"

3. If all you wanted to do was use the array/list as input to an IN clause in a Where statement you could use :-

3、如果你想在IN子句裡使用輸入的數組參數可以這樣做：

Create PROCEDURE sp_MyProcedure (@MyCommaDelimitedString
Varchar(255))
AS
BEGIN
EXEC ('Select * FROM MYTABLE Where MYFIELD IN (' + @MyCommaDelimitedString + ')')
END
GO
 
 
 
 

文章來自: Tank部落格
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相關日誌: